import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Properties;
import java.util.StringTokenizer;
import java.io.File;
import java.io.*;
import java.sql.*;
import com.mysql.jdbc.Driver;
import java.util.*;
import java.text.SimpleDateFormat;
import java.text.ParseException;

/*
 * 
 * This class contains methods to read CSV file and update records in database.
 * 
 */

public class ReadWeatherFile {

	String fileName;
	Properties dbProperties = new Properties();
	Connection conn;
	String stationID;
	
	// Constructor to set CSV date file name
	public ReadWeatherFile(String FileName)
	{
		this.fileName=FileName;
	}
	
	/**
     * Load a Properties File which has database connectivity properties and stattionID for this client
     */
    public void loadDBProperties(String propFileName) throws IOException {
        
    	File propsFile = new File(propFileName);
        FileInputStream fis = new FileInputStream(propsFile);
        dbProperties.load(fis);    
        fis.close();
        
    }
	
	public void readFileAndUpdateDB()
	{
		
		try {
			  System.out.println("Loaded properties are:");
			  System.out.println(dbProperties);
			  
			  // Load database driver class			  
		      Class.forName(dbProperties.getProperty("driver"));
		      
		      System.out.println("loaded driver.. proceeding");
		      System.out.println("Using URL:  " +dbProperties.getProperty("dburl")+dbProperties.getProperty("dbName")+"?user="+ dbProperties.getProperty("user") +"&password=" + dbProperties.getProperty("password"));
		      // It would be in below format after reading from properties file
		      //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/weatherDB?user=root&password=");
		     
		      // Create Mysql database connection.
		      conn = DriverManager.getConnection(dbProperties.getProperty("dburl")+dbProperties.getProperty("dbName")+"?user="+ dbProperties.getProperty("user") +"&password=" + dbProperties.getProperty("password"));
		      System.out.println("Connected to the database successfully");
		      
		      // Get stattionID from property file
		      stationID = dbProperties.getProperty("stattionID");
		      int numOfRecords = 0;
		      
		      // Updating below columns as of now based on Weather data file, more columns can be easily added in future
		      String sqlString = "insert into weather(stationid,reporteddate,direction,speed,temparature) values(?,?,?,?,?)";
		      PreparedStatement pst = conn.prepareStatement(sqlString);
		      
		      try {
		    	  
		    	  System.out.println("Starting CSV Processing");
					// Loading CSV file for reading
					BufferedReader br = new BufferedReader( new FileReader(fileName));
				
					StringTokenizer st = null;
					int lineNumber = 0;
					String dataLine = new String();
		 
					while( (dataLine = br.readLine()) != null)
					{
						
						// Skip first line as it contains column headers name, NOT DATA
						if(lineNumber == 0)
						{
							lineNumber++;
							continue;
						}
						
						//break comma separated values in line using ","
						st = new StringTokenizer(dataLine, ",");

						while(st.hasMoreTokens())
						{	
							
							// Reading format of CSV File in the order
							// Number ,Date,Time ,Direction,Speed(m/s),Temperature ,Station ID
							String recordNo = st.nextToken();
							String dateStr  = st.nextToken();
							String timeStr  = st.nextToken();
							String directionStr = st.nextToken();
							String speedStr = st.nextToken();
							String temparatureStr = st.nextToken();
							String configuredStationID = st.nextToken();
							
							String mysqlDateString = "";
																						
							
							System.out.println("Data read: "+ recordNo + " "+ dateStr + " "+timeStr+" "+directionStr+" "+speedStr+" "+temparatureStr);
							
							// Create a Date object by combining below format in CSV file.
							SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
							java.util.Date parsed;

					       try {
					            parsed = format.parse(dateStr+ " "+timeStr);
					            System.out.println(" Parsed date from CSV data: " + parsed.toString());
					            
					           // Create a datetime String to insert as per mysql Datetime default format below.
					            SimpleDateFormat formatter  = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss");
					            mysqlDateString = formatter.format(parsed);
					        }
					        catch(ParseException pe) {
					            System.out.println("ERROR: Cannot parse");
					        }
						 
					        // Set column values for inserting.
					        pst.setString(1,stationID);
					        pst.setString(2,mysqlDateString);
					        pst.setString(3,directionStr);
					        pst.setString(4,speedStr);
					        pst.setString(5,temparatureStr);
					        
					        try{
					        numOfRecords = pst.executeUpdate();
					        }catch(SQLException sqlEX)
					        {
					        	System.out.println("SQL error message:"+ sqlEX.getMessage());
					        	System.out.println("Proceeding with next record");
					        }
					        
					        if(numOfRecords ==1 )
					        {
					        	System.out.println("Record processed");
					        }else
					        {
					        	System.out.println("Unsuccessful processing of record");
					        }
					        
						}
		 
						lineNumber++;
						
					}
				} catch (FileNotFoundException e) {
					System.out.println(" Given File Name "+ fileName +"not found in program directory. Please check it's present and name is correct");
					e.printStackTrace();
					
				} catch (IOException e) {

					System.out.println("Error reading file");
					e.printStackTrace();
				}
		        
		      
				// Close prepared statement and database connection objects
		        pst.close();
				conn.close();
		      System.out.println("Disconnected from database");
		    } catch (Exception e) {
		      System.out.println("An error occured during database processing");	
		      e.printStackTrace();
		    }
	}
    
		

	//Set and Get methods 
	public void setFileName(String newFileName)
	{
		this.fileName=newFileName;
	}
	public String getFileName()
	{
		return fileName;
	}
	
	
}